<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<%@ page language="java" import="net.sf.json.JSONArray" %>
<%@ page language="java" import="net.sf.json.JSONObject" %>
<%
try {
String connectionURL = "jdbc:mysql://webproject2.cse.ust.hk/BookwarmMobile?user=BookwarmMobile&password=bookwarm";

Connection connection = null;

Class.forName("com.mysql.jdbc.Driver").newInstance();

connection = DriverManager.getConnection(connectionURL);

if(!connection.isClosed()){
String newCopyID = null;
String newTransDate = null;
String newStatus = null;
String newISBN = null;
String newCopyCondition = null;
String newCopyPrice = null;
String newBookTitle = null;
String newEdition = null;
String newCourses = null;
String newAuthors = null;
String newPublisher = null;
String newAccountName = null;
JSONArray arrayObj=new JSONArray();

newAccountName = request.getParameter("username");

// sell history
Statement statement0 = null;
ResultSet rs0 = null; 
statement0 = connection.createStatement();
String query0 = "SELECT * FROM Sell WHERE AccountName = '" + newAccountName + "' AND Status <> 'Canceled';";
rs0 = statement0.executeQuery(query0);
while(rs0.next()) {
newCopyID = rs0.getString("Sell.CopyID");
newTransDate = rs0.getString("Sell.SellDate");
newStatus = rs0.getString("Sell.Status");

Statement statement1 = null;
ResultSet rs1 = null;
statement1 = connection.createStatement();
String query1 = "SELECT * FROM BookCopies WHERE CopyID = " + newCopyID + ";";
rs1 = statement1.executeQuery(query1);
if(rs1.next()) {
newISBN = rs1.getString("BookCopies.ISBN");
newCopyCondition = rs1.getString("BookCopies.CopyCondition");
newCopyPrice = rs1.getString("BookCopies.CopyPrice");

}
statement1.close();

Statement statement2 = null;
ResultSet rs2 = null;
statement2 = connection.createStatement();
String query2 = "SELECT * FROM Books WHERE ISBN = '" + newISBN + "';";
rs2 = statement2.executeQuery(query2);
if(rs2.next()) {
newBookTitle = rs2.getString("Books.BookTitle");
newEdition = rs2.getString("Books.Edition");
newCourses = rs2.getString("Books.Courses");
newAuthors = rs2.getString("Books.Authors");
newPublisher = rs2.getString("Books.Publisher");

}
statement2.close();

JSONObject first = new JSONObject();
first.put("CopyID", newCopyID);
first.put("ISBN", newISBN);
first.put("Condition", newCopyCondition);
first.put("Price", newCopyPrice);
first.put("Title", newBookTitle);
first.put("Edition", newEdition);
first.put("Courses", newCourses);
first.put("Authors", newAuthors);
first.put("Publisher", newPublisher);
first.put("Date", newTransDate);
first.put("Status", newStatus);

arrayObj.add(first);

} 
statement0.close();

// lend history
Statement statement3 = null;
ResultSet rs3 = null; 
statement3 = connection.createStatement();
String query3 = "SELECT * FROM Lend WHERE AccountName = '" + newAccountName + "' AND Status <> 'Canceled';";
rs3 = statement3.executeQuery(query3);
while(rs3.next()) {
newCopyID = rs3.getString("Lend.CopyID");
newTransDate = rs3.getString("Lend.LendDate");
newStatus = rs3.getString("Lend.Status");

Statement statement4 = null;
ResultSet rs4 = null;
statement4 = connection.createStatement();
String query4 = "SELECT * FROM BookCopies WHERE CopyID = " + newCopyID + ";";
rs4 = statement4.executeQuery(query4);
if(rs4.next()) {
newISBN = rs4.getString("BookCopies.ISBN");
newCopyCondition = rs4.getString("BookCopies.CopyCondition");
newCopyPrice = rs4.getString("BookCopies.CopyPrice");

}
statement4.close();

Statement statement5 = null;
ResultSet rs5 = null;
statement5 = connection.createStatement();
String query5 = "SELECT * FROM Books WHERE ISBN = '" + newISBN + "';";
rs5 = statement5.executeQuery(query5);
if(rs5.next()) {
newBookTitle = rs5.getString("Books.BookTitle");
newEdition = rs5.getString("Books.Edition");
newCourses = rs5.getString("Books.Courses");
newAuthors = rs5.getString("Books.Authors");
newPublisher = rs5.getString("Books.Publisher");

}
statement5.close();

JSONObject first = new JSONObject();
first.put("CopyID", newCopyID);
first.put("ISBN", newISBN);
first.put("Condition", newCopyCondition);
first.put("Price", newCopyPrice);
first.put("Title", newBookTitle);
first.put("Edition", newEdition);
first.put("Courses", newCourses);
first.put("Authors", newAuthors);
first.put("Publisher", newPublisher);
first.put("Date", newTransDate);
first.put("Status", newStatus);

arrayObj.add(first);

} 
statement3.close();

out.print(arrayObj);

}

connection.close();

}
catch(Exception ex){
out.print(ex);

}
%>